package com.gr.search;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ManagerRecord {

    private Connection con;

    public ManagerRecord() {
        try {
            con = Connector.connect();
        } catch (ClassNotFoundException e) {
            System.err.println("Can't connect");
        } catch (SQLException e) {
            System.err.println("Database error");
            e.printStackTrace();
        }
    }

    public void insertLoc(LocationRecord lc) {
        try {
            System.out.println(lc.getX() + "-" + lc.getY());
            java.sql.PreparedStatement stmt = con.prepareStatement("INSERT INTO location(p,docID) VALUES(point(?, ?), ?);");
            stmt.setFloat(1, lc.getX());
            stmt.setFloat(2, lc.getY());
            stmt.setInt(3, lc.getDocID());
            stmt.executeUpdate();
        } catch (SQLException e) {
            //		con.close();
            System.err.println("Database error");
            e.printStackTrace();
        }
    }

    //the point with longtitude x and latitude y, docID=k
    public List<LocationRecord> getKNearest(float x, float y, int k) {
        List<LocationRecord> resultList = new ArrayList<LocationRecord>();
        try {
            
            //java.sql.PreparedStatement stmt = con.prepareStatement("SELECT docID, AsText(p) from location ORDER BY distance(Point(?,?),p) LIMIT ?;");
            java.sql.PreparedStatement stmt = con.prepareStatement("SELECT docID, AsText(p) from location ORDER BY distance(Point(?,?),p) LIMIT ?;");
            stmt.setFloat(1, x);
            stmt.setFloat(2, y);
            stmt.setInt(3, k);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                int docID = rs.getInt(1);
                String point = rs.getString(2);
                point = point.substring(6, point.length() - 1);
                String[] location = point.split(" ");
                Float longitdue = new Float(location[0]);
                Float latitdue = new Float(location[1]);
                LocationRecord newRecord = new LocationRecord(longitdue.floatValue(), latitdue.floatValue(), docID);
                resultList.add(newRecord);
                System.out.println(docID + " " + location[0] + " " + location[1]);
            }

        } catch (SQLException e) {
            System.err.println("Database error");
            e.printStackTrace();
        }
        return resultList;
    }

    public int getLastDocID() {
        int last = 0;
        try {
            java.sql.PreparedStatement stmt = con.prepareStatement("SELECT docID from location ORDER BY docID DESC LIMIT 1;");
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                last = rs.getInt(1);
            }

        } catch (SQLException e) {
            System.err.println("Database error");
            e.printStackTrace();
        }
        return last;
    }
}
